using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.Text;
using System.Windows.Forms;
using ADOX;

namespace Presentation
{
    /// <summary>
    /// Backup data class: used to back up data, create backup file and copy data of some 
    ///                     important table
    /// </summary>
    public class BackupData
    {
        /// <summary>
        /// 
        /// </summary>
        private static string _path;
        public BackupData(string path)
        {
            _path = path;
        }

        public string Path
        {
            get { return _path; }
        }

        public bool CreateBackupDatabase()
        {
            CatalogClass cat = new CatalogClass();
            string backupFile = "backup_dulieu.mdb";
            string sqlConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                                   "Data Source=" + _path + backupFile + ";Jet OLEDB:Engine Type=5";
            
            //backup
            try
            {
                cat.Create(sqlConnection);

                CreateDuocphamTable(sqlConnection);
                CreateDonviduocphamTable(sqlConnection);
                CreateCtynhaphangTable(sqlConnection);
                CreateBSketoaTable(sqlConnection);
                CreateHoadonTable(sqlConnection);
                CreateCtHoadonmuaTable(sqlConnection);
                CreateHinhthucbanTable(sqlConnection);
                CreateCtHinhthucbanTable(sqlConnection);
                CreateNhanvienTable(sqlConnection);
                CreatePhanquyenTable(sqlConnection);
                CreateSoloTable(sqlConnection);
                CreateTientrongngayTable(sqlConnection);
                CreateLoaiduocphamTable(sqlConnection);

                //create view
                CreateViewTongsotienToabacsi(sqlConnection);
                CreateViewTongsotoaToabacsi(sqlConnection);
                CreateViewGetToaBacSi(sqlConnection);
                CreateViewHangcanngay(sqlConnection);
                return true;
            }
            catch (Exception exception)
            {
                //throw new Exception(exception.Message);
                return false;
            }
            
        }
        /// <summary>
        /// Backup for table Duocpham: structure + data
        /// </summary>
        /// <param name="connection"></param>
        private void CreateDuocphamTable(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);
            try
            {
                connection.Open();
                string query = "CREATE TABLE [Duocpham] ( [ID] AUTOINCREMENT, [Maduocpham] TEXT(255) WITH COMPRESSION," +
                    " [Tenduocpham] TEXT(255) WITH COMPRESSION,[Noisanxuat] TEXT(255) WITH COMPRESSION," +
                    " [MaCtynhaphang] LONG, [Madonvi] LONG,[Maloaiduocpham] LONG,[Gianhap] DOUBLE," +
                    " [Giaxuat] DOUBLE, [Soluongtoithieu] LONG, [Soluongtonkho] LONG,CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID]));";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
                //Insert data for backup
                query = "INSERT INTO [Duocpham] SELECT * FROM [MS Access;DATABASE=" + _path + "\\dulieu.mdb;].[Duocpham]";
                command.CommandText = query;
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }finally
            {
                connection.Close();
            }
            
        }
        /// <summary>
        /// backup for Donviduocpham
        /// </summary>
        /// <param name="connection"></param>
        private void CreateDonviduocphamTable(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);
            
            try
            {
                connection.Open();
                string query = "CREATE TABLE [Donviduocpham] ( " +
                           "  [ID] AUTOINCREMENT," +
                           "  [Tendonvi] TEXT(255) WITH COMPRESSION," +
                           "  CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID]));";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
                //Insert data for backup
                query = "INSERT INTO [Donviduocpham] SELECT * FROM [MS Access;DATABASE=" + _path + "\\dulieu.mdb;].[Donviduocpham]";
                command.CommandText = query;
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// backup for Ctynhaphang
        /// </summary>
        /// <param name="connection"></param>
        private void CreateCtynhaphangTable(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);
            
            try
            {
                connection.Open();
                string query = "CREATE TABLE [Ctynhaphang] (" +
                           " [ID] AUTOINCREMENT," +
                           " [Tencongty] TEXT(255) WITH COMPRESSION," +
                           " CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID]))";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
                //Insert data for backup
                query = "INSERT INTO [Ctynhaphang] SELECT * FROM [MS Access;DATABASE=" + _path + "\\dulieu.mdb;].[Ctynhaphang]";
                command.CommandText = query;
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// backup for bacsi
        /// </summary>
        /// <param name="connection"></param>
        private static void CreateBSketoaTable(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);
            
            try
            {
                connection.Open();
                string query = "CREATE TABLE [BSketoa] (" +
                           " [ID] AUTOINCREMENT," +
                           " [Tenbacsiketoa] TEXT(255) WITH COMPRESSION," +
                           " CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID]))";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
                //Insert data for backup
                query = "INSERT INTO [BSketoa] SELECT * FROM [MS Access;DATABASE=" + _path + "\\dulieu.mdb;].[BSketoa]";
                command.CommandText = query;
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    
        private void CreateBanhangTable(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);

            try
            {
                connection.Open();
                string query = "CREATE TABLE [Banhang] (" +
                               " [ID] AUTOINCREMENT," +
                               " [HoadonID] TEXT(255) WITH COMPRESSION," +
                               " [HinhthucbanID] TEXT(255) WITH COMPRESSION," +
                               " CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])" +
                               " );";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        private static void CreateCtHinhthucbanTable(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);

            try
            {
                connection.Open();
                string query = "CREATE TABLE [CtHinhthucban] (" +
                               " [ID] AUTOINCREMENT," +
                               " [HinhthucbanID] LONG," +
                               " [DuocphamID] LONG," +
                               " [Soluong] SMALLINT," +
                               " [Thanhtien] DOUBLE," +
                               " CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])" +
                               " );";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        
        private static void CreateCtHoadonmuaTable(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);

            try
            {
                connection.Open();
                string query = "CREATE TABLE [CTHoadonmua] (" +
                               " [ID] AUTOINCREMENT," +
                               " [HoadonID] LONG," +
                               " [DuocphamID] LONG," +
                               " [Soluongmua] SMALLINT," +
                               " [Thanhtien] DOUBLE," +
                               " CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])" +
                               " );";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        
        private static void CreateHinhthucbanTable(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);

            try
            {
                connection.Open();
                string query = "CREATE TABLE [Hinhthucban] (" +
                               " [ID] AUTOINCREMENT," +
                               " [HoadonID] LONG," +
                               " [Tenbenhnhan] TEXT(255) WITH COMPRESSION," +
                               " [Trieuchung] TEXT(255) WITH COMPRESSION," +
                               " [BacsiketoaID] LONG," +
                               " [Cachban] TINYINT," +
                               " CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])" +
                               " );";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        private static void CreateHoadonTable(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);

            try
            {
                connection.Open();
                string query = "CREATE TABLE [Hoadon] ( " +
                               " [ID] AUTOINCREMENT," +
                               " [Mahoadon] TEXT(255) WITH COMPRESSION," +
                               " [Manhanvien] LONG," +
                               " [Ngaytao] DATETIME," +
                               " [Tongtien] DOUBLE," +
                               " [VAT] LONG," +
                               " [Sotienthanhtoan] LONG," +
                               " [Loaihoadon] TINYINT," +
                               " CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])" +
                               " );";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        private static void CreateLoaiduocphamTable(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);

            try
            {
                connection.Open();
                string query = "CREATE TABLE [Loaiduocpham] (" +
                               " [ID] AUTOINCREMENT," +
                               " [Tenloaiduocpham] TEXT(255) WITH COMPRESSION," +
                               " CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])" +
                               " );";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
                //Insert data for backup
                query = "INSERT INTO [Loaiduocpham] SELECT * FROM [MS Access;DATABASE=" + _path + "\\dulieu.mdb;].[Loaiduocpham]";
                command.CommandText = query;
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        private static void CreateNhanvienTable(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);

            try
            {
                connection.Open();
                string query = "CREATE TABLE [Nhanvien] (" +
                               " [ID] AUTOINCREMENT," +
                               " [Manhanvien] TEXT(255) WITH COMPRESSION," +
                               " [Username] TEXT(255) WITH COMPRESSION," +
                               " [Password] TEXT(255) WITH COMPRESSION," +
                               " [Tennhanvien] TEXT(255) WITH COMPRESSION," +
                               " [Dienthoai] TEXT(255) WITH COMPRESSION," +
                               " [Diachi] TEXT(255) WITH COMPRESSION," +
                               " [Maphanquyen] LONG," +
                               " [CMND] TEXT(255) WITH COMPRESSION," +
                               " CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])" +
                               " );";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
                //Insert data for backup
                query = "INSERT INTO [Nhanvien] SELECT * FROM [MS Access;DATABASE=" + _path + "\\dulieu.mdb;].[Nhanvien]";
                command.CommandText = query;
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        private static void CreatePhanquyenTable(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);

            try
            {
                connection.Open();
                string query = "CREATE TABLE [Phanquyen] ( " +
                               " [ID] AUTOINCREMENT," +
                               " [Tenphanquyen] TEXT(255) WITH COMPRESSION," +
                               " CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])" +
                               " );";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
                //Insert data for backup
                query = "INSERT INTO [Phanquyen] SELECT * FROM [MS Access;DATABASE=" + _path + "\\dulieu.mdb;].[Phanquyen]";
                command.CommandText = query;
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        private static void CreateSoloTable(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);

            try
            {
                connection.Open();
                string query = "CREATE TABLE [Solo] ( " +
                               " [ID] AUTOINCREMENT," +
                               " [DuocphamID] LONG," +
                               " [HoadonmuaID] LONG," +
                               " [Solo] TEXT(255) WITH COMPRESSION," +
                               " [Soluongnhap] LONG," +
                               " [Hansudung] DATETIME," +
                               " [Ngaysanxuat] DATETIME," +
                               " CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])" +
                               " );";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
                //Insert data for backup
                query = "INSERT INTO [Solo] SELECT * FROM [MS Access;DATABASE=" + _path + "\\dulieu.mdb;].[Solo]";
                command.CommandText = query;
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        private static void CreateTientrongngayTable(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);

            try
            {
                connection.Open();
                string query = "CREATE TABLE [Tientrongngay] ( " +
                               " [ID] AUTOINCREMENT," +
                               " [Ngayban] DATETIME," +
                               " [Tongtien] DOUBLE," +
                               " CONSTRAINT [PrimaryKey] PRIMARY KEY ([ID])" +
                               " );";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        private static void CreateViewGetToaBacSi(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);

            try
            {
                connection.Open();
                string query =
                    "CREATE VIEW [View_GetToaBacSi] AS SELECT tien.Tenbacsiketoa, Sum(tien.Tongsotienthanhtoan) AS SumOfTongsotienthanhtoan, Sum(toa.CountOfID) AS SumOfCountOfID" +
                    " FROM View_Tongsotien_Toabacsi AS tien INNER JOIN View_Tongsotoa_Toabacsi AS toa ON (tien.ID = toa.ID) AND (tien.Ngaytao = toa.Ngaytao)" +
                    " GROUP BY tien.Tenbacsiketoa;";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        private static void CreateViewTongsotienToabacsi(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);

            try
            {
                connection.Open();
                string query =
                    "CREATE VIEW [View_Tongsotien_Toabacsi] AS SELECT BSketoa.Tenbacsiketoa, Sum(Hoadon.Sotienthanhtoan) AS Tongsotienthanhtoan, BSketoa.ID, Hoadon.Ngaytao" +
                    " FROM (Hinhthucban INNER JOIN BSketoa ON Hinhthucban.BacsiketoaID = BSketoa.ID) INNER JOIN Hoadon ON Hinhthucban.HoadonID = Hoadon.ID" +
                    " GROUP BY BSketoa.Tenbacsiketoa, BSketoa.ID, Hoadon.Ngaytao;";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        private static void CreateViewTongsotoaToabacsi(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);

            try
            {
                connection.Open();
                string query =
                    "CREATE VIEW [View_Tongsotoa_Toabacsi] AS SELECT BSketoa.Tenbacsiketoa, Count(BSketoa.ID) AS CountOfID, Hoadon.Ngaytao, BSketoa.ID" +
                    " FROM (Hinhthucban INNER JOIN BSketoa ON Hinhthucban.BacsiketoaID = BSketoa.ID) INNER JOIN Hoadon ON Hinhthucban.HoadonID = Hoadon.ID" +
                    " GROUP BY BSketoa.Tenbacsiketoa, Hoadon.Ngaytao, BSketoa.ID;";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }
        private static void CreateViewHangcanngay(string sqlConnection)
        {
            OleDbConnection connection = new OleDbConnection(sqlConnection);

            try
            {
                connection.Open();
                string query =
                    "CREATE VIEW [View_Hangcanngay] AS SELECT dp.Tenduocpham, dp.Soluongtonkho, dp.Noisanxuat, dp.Gianhap, sl.Hansudung" +
                    " FROM Duocpham AS dp, Solo AS sl" +
                    " WHERE (((sl.DuocphamID)=[dp].[ID]));";
                OleDbCommand command = new OleDbCommand(query, connection);
                command.ExecuteNonQuery();
            }
            catch (Exception exception)
            {
                throw new Exception(exception.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    }
}
